package com.bw.mapper;

import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;
import java.util.Objects;

@Mapper
public interface GoodsMpper {
    //a)	查询商品表,并展示商家名称（5分）
    @Select("select * from goods g left join businesses b on g.business_id = b.id")
    List<Map<String, Objects>> select1();
    //b)	查询所有的商品,并按照价格区间查询商品（5分）
    @Select("select * from goods where price between #{min} and #{max}")
    List<Map<String, Objects>> select2(@Param("min") double min, @Param("max") double max);
    //c)	查询一共有多少商品（5分）
    @Select("select count(*) from goods")
      Map<String, Objects> select3();
    //d)	查询每个商家各有几种商品（5分）
    @Select("select b.id,b.name,count(*) from goods g left join businesses b on g.business_id = b.id  group by b.id, b.name ")
    Map<String, Objects> select4();
    //e)	查询商品价格大于平均价格的商品信息（5分）
    @Select("select * from goods where price>(select avg(price) from goods) ")
    Map<String, Objects> select5();
    //f)	删除某一个商品（5分）
    @Delete("delete from goods where id=#{id}")
    Map<String, Objects> delectId(Integer id);
}
